﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Word = Microsoft.Office.Interop.Word;
using System.Reflection;


namespace SQLHCReportGen
{

    class IndexFragUnit
    {
        public string sIndexName;
        public string sTableName;
        public string sDBName;
        public IndexFragUnit(string i, string t, string d) { sIndexName = i; sTableName = t; sDBName = d; }
    }

    class StatisticsUpdateInfoUnit
    {
        public string sTableName;
        public string sDBName;
        public DateTime dStatisticsUpdateDate;
        public string sStatsName;
        public StatisticsUpdateInfoUnit(string t, string d, DateTime ds, string s) { sTableName = t; sDBName = d; dStatisticsUpdateDate = ds; sStatsName = s; }
    }

    class MissingIndexUnit
    {
        public int iDBID;
        public string sCreateIndexStatement;
        public int iImprovementMeasure;
        public MissingIndexUnit(int d, string c, int i) { iDBID = d; sCreateIndexStatement = c; iImprovementMeasure = i; }
    }

    class PrivateKeyBackupUnit
    {
        public DateTime dKeyBackupTime;
        public string sEncryptedDatabase;
        public string sCertificateName;
        public PrivateKeyBackupUnit(DateTime k, string e, string c) { dKeyBackupTime = k; sEncryptedDatabase = e; sCertificateName = c; }
    }

    class Program
    {
        static SQLInfo SQLInfoObj = new SQLInfo();
        static void Main(string[] args)
        {
            if (args.Length == 0)
            {
                Console.WriteLine("xml文件路径和公司名字采用默认属性");
                SQLInfoObj.mXMLFilePath = "D:\\xx.xml";
                SQLInfoObj.mCompanyName = "Microsoft";
            }
            else if (args.Length == 1)
            {
                Console.WriteLine("公司名字采用默认属性");
                SQLInfoObj.mCompanyName = "Microsoft";
            }
            else if (args.Length == 2)
            {
                SQLInfoObj.mXMLFilePath = args[0];
                SQLInfoObj.mCompanyName = args[1];
            }
            else
                Console.WriteLine("参数错误");
            //Process(args[0]);
            if (Process(SQLInfoObj.mXMLFilePath))
                GenReport(SQLInfoObj.mCompanyName);
        }
        public static bool Process(String inPath)
        {
            XmlDocument doc = new XmlDocument();
            try
            {
                doc.Load(inPath);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Can't find the xml file!");
                return false;
            }
            XmlNode rootComputerName = doc.ChildNodes[0];
            //System.Console.WriteLine(root.Attributes["name"].Value);
            string full_string = rootComputerName.Attributes["name"].Value;
            SQLInfoObj.mComputerName = full_string.Substring(0, full_string.Length - 2);
            //Console.WriteLine("----" + SQLInfoObj.mComputerName);
            SQLInfoObj.mPlatformBit = Convert.ToInt32(full_string.Substring(full_string.Length - 2, 2));
            //Console.WriteLine("----" + SQLInfoObj.mComputerName + "---" + SQLInfoObj.mPlatformBit);
            Console.WriteLine(SQLInfoObj.mComputerName);
            XmlNode InstanceName = rootComputerName.ChildNodes[0];
            Console.WriteLine(InstanceName.Name);
            XmlNodeList xnl = InstanceName.ChildNodes;
            foreach (XmlNode xn in xnl)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "System": LoadSystem(xe); break;
                    case "SystemMaint": LoadSystemMaint(xe); break;
                    case "DBConfig": LoadDBConfig(xe); break;
                    case "LoginPWD": LoadLoginPWD(xe); break;
                    case "RuntimeCheck": LoadRuntime(xe); break;
                    case "DBOptions": LoadDBOptions(xe); break;
                    case "PerfCheck": LoadPerf(xe); break;
                    case "IndexCheck": LoadIndex(xe); break;

                }

            }
            return true;
        }

        public static void LoadSystem(XmlElement System)
        {
            Console.WriteLine("System loads");
            XmlNodeList SystemList = System.ChildNodes;
            foreach (XmlNode xn in SystemList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "DC":
                        {
                            SQLInfoObj.mDC = Convert.ToInt32(xe.InnerText);
                        } break;
                    case "Protocols":
                        {
                            if (xe.HasChildNodes)
                            {
                                SQLInfoObj.mTCP = Convert.ToInt32(xe.FirstChild.InnerText);
                                SQLInfoObj.mNP = Convert.ToInt32(xe.LastChild.InnerText);
                                //Console.WriteLine(SQLInfoObj.mTCP);
                            }
                            else Console.WriteLine("no protocol info");
                        } break;
                    case "SQLSP":
                        {
                            SQLInfoObj.mSQLSP = xe.InnerText;
                            //Console.WriteLine(SQLInfoObj.mSQLSP);
                        } break;
                }
            }
        }

        public static void LoadSystemMaint(XmlElement SystemMaint)
        {
            Console.WriteLine("SystemMaint loads");
            XmlNodeList SystemMaintList = SystemMaint.ChildNodes;
            foreach (XmlNode xn in SystemMaintList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "LastKnownGoodCheckDB":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mDLastKnowGoodCheckDB.Add(xe2.GetAttribute("name"), Convert.ToDateTime(xe2.GetAttribute("dbccLastKnownGood")));
                            }
                            /*  foreach (KeyValuePair<string, DateTime> a in SQLInfoObj.mDLastKnowGoodCheckDB)
                                  Console.WriteLine(a.Key + a.Value);*/
                        } break;
                    case "PrivateKeyBackup":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mPrivateKeyBackup.Add(new PrivateKeyBackupUnit(Convert.ToDateTime(xe2.GetAttribute("pvt_key_last_backup_date")), xe2.GetAttribute("encrypteddatabase"), xe2.GetAttribute("Certificate_Name")));
                            }
                        } break;
                    case "DBWithoutBackup":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mDBNameWithoutBackup.Add(xe2.GetAttribute("Name"));
                            }
                            //  Console.WriteLine(SQLInfoObj.mDBNameWithoutBackup.Count);
                        } break;
                    case "DBWithoutLogBackup":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mDBNameWithoutLogBackup.Add(xe2.GetAttribute("Name"));
                            }
                        } break;
                    case "NumOfTempDB":
                        {
                            SQLInfoObj.mNumOfTempdb = Convert.ToInt32(xe.GetAttribute("num"));
                            SQLInfoObj.mNumbOfCPU = Convert.ToInt32(xe.GetAttribute("cpu"));
                            //Console.WriteLine(SQLInfoObj.mNumOfTempdb + SQLInfoObj.mNumbOfCPU);
                        } break;
                    case "SQLTrace":
                        {
                            SQLInfoObj.mSQLTrace = Convert.ToInt32(xe.InnerText);
                        } break;
                }
            }

        }

        public static void LoadDBConfig(XmlElement DBConfig)
        {
            Console.WriteLine("DBConfig loads");
            XmlNodeList SystemList = DBConfig.ChildNodes;
            foreach (XmlNode xn in SystemList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "AffinityMask": SQLInfoObj.mAffinityMask = Convert.ToInt32(xe.InnerText); break;
                    case "AWE": SQLInfoObj.mAWE = Convert.ToInt32(xe.InnerText); break;
                    case "C2Audit": SQLInfoObj.mC2 = Convert.ToInt32(xe.InnerText); break;
                    case "CrossDBOwnership": SQLInfoObj.mCorssDBOwnership = Convert.ToInt32(xe.InnerText); break;
                    case "DefaultTrace": SQLInfoObj.mDefaultTrace = Convert.ToInt32(xe.InnerText); break;
                    case "LightweightPooling": SQLInfoObj.mLightWeightPooling = Convert.ToInt32(xe.InnerText); break;
                    case "Locks": SQLInfoObj.mLocks = Convert.ToInt32(xe.InnerText); break;
                    case "MaxDOP": SQLInfoObj.mMaxDOP = Convert.ToInt32(xe.InnerText); break;
                    case "MaxServerMemory": SQLInfoObj.mMaxServerMemory = Convert.ToInt32(xe.InnerText); break;
                    case "MaxWorkerThreads": SQLInfoObj.mMaxWorkerThread = Convert.ToInt32(xe.InnerText); break;
                    case "NetworkPacketSize": SQLInfoObj.mNetworkPacketSize = Convert.ToInt32(xe.InnerText); break;
                    case "PriorityBoost": SQLInfoObj.mPriorityBoost = Convert.ToInt32(xe.InnerText); break;
                    case "SetWorkingsetSize": SQLInfoObj.mSetWorkingsetSize = Convert.ToInt32(xe.InnerText); break;
                    case "PhysicalMemory": SQLInfoObj.mPhysicalMemory = Convert.ToInt32(xe.InnerText); break;
                }

            }
        }
        public static void LoadLoginPWD(XmlElement LoginPWD)
        {
            Console.WriteLine("LoginPWD loads");
            XmlNodeList LoginPWDList = LoginPWD.ChildNodes;
            foreach (XmlNode xn in LoginPWDList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {

                    case "PasswordPolicy":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mLoginNamePasswordPolicy.Add(xe2.InnerText);
                            }
                        } break;
                    case "CheckAdminPermission":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mCheckAdminPermission.Add(xe2.GetAttribute("Name"), Convert.ToInt32(xe2.GetAttribute("IsAdmin")));
                            }
                        } break;
                    case "BlankPassword":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mLoginNameWithBlankPWD.Add(xe2.InnerText);
                            }
                        } break;
                    case "PwdSameAsUserName":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mLoginPwdSameAsUserName.Add(xe2.InnerText);
                            }
                        } break;
                }
            }

        }

        public static void LoadRuntime(XmlElement Runtime)
        {
            Console.WriteLine("Runtime loads");
            XmlNodeList RuntimeList = Runtime.ChildNodes;
            foreach (XmlNode xn in RuntimeList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "Nonyielding": SQLInfoObj.mNonYielding = Convert.ToInt32(xe.InnerText); break;
                    case "AV": SQLInfoObj.mAV = Convert.ToInt32(xe.InnerText); break;
                    case "MemoryTrimming": SQLInfoObj.mMemTrimming = Convert.ToInt32(xe.InnerText); break;
                    case "IOBottleneck": SQLInfoObj.mIOBottleNeck = Convert.ToInt32(xe.InnerText); break;
                    case "UserTokenCache": SQLInfoObj.mUserTokenCacheSize = Convert.ToInt32(xe.InnerText); break;
                    case "VirtualLogFile":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mVLF.Add(xe2.GetAttribute("Name"), Convert.ToInt32(xe2.GetAttribute("Num")));
                            }
                        } break;
                }
            }
        }

        public static void LoadDBOptions(XmlElement DBOptions)
        {
            Console.WriteLine("DBOptions loads");
            XmlNodeList DBOptionsList = DBOptions.ChildNodes;
            foreach (XmlNode xn in DBOptionsList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "CompatibilityLevel":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mCompatibilityLevel.Add(xe2.GetAttribute("Name"), Convert.ToInt32(xe2.GetAttribute("CompatibilityLevel")));
                            }

                        } break;
                    case "AutoClose":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {

                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mAutoClose.Add(xe2.GetAttribute("Name"));
                            }
                        } break;
                    case "PageVerify":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mPageVerify.Add(xe2.GetAttribute("Name"));
                            }
                        } break;
                    case "GrowByPercentage":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mGroupByPercentage.Add(xe2.GetAttribute("Name"));
                            }

                        } break;
                }
            }
        }

        public static void LoadPerf(XmlElement Perf)
        {
            Console.WriteLine("Perf loads");
            XmlNodeList PerfList = Perf.ChildNodes;
            foreach (XmlNode xn in PerfList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "PageLifeExpectancy": SQLInfoObj.mPageLifeExpectancy = Convert.ToInt32(xe.InnerText); break;
                    case "Blocking": SQLInfoObj.mBlocking = Convert.ToInt32(xe.InnerText); break;
                    case "CPU_UT": SQLInfoObj.mCPU_UT = Convert.ToInt32(xe.InnerText); break;
                    case "DiskPerformance":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mDiskPerformance.Add(Convert.ToDateTime(xe2.GetAttribute("Time")), Convert.ToInt32(xe2.GetAttribute("DiskSec")));
                            }
                        } break;
                    case "DeadlockCheck": SQLInfoObj.mDeadLock = Convert.ToInt32(xe.InnerText); break;
                }
            }
        }

        public static void LoadIndex(XmlElement IndexInfo)
        {
            Console.WriteLine("IndexInfo loads");
            XmlNodeList IndexInfoList = IndexInfo.ChildNodes;
            foreach (XmlNode xn in IndexInfoList)
            {
                XmlElement xe = (XmlElement)xn;
                switch (xe.Name)
                {
                    case "IndexFrag":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mIndexFrag.Add(new IndexFragUnit(xe2.GetAttribute("IndexName"), xe2.GetAttribute("TableName"), xe2.GetAttribute("DBName")));
                            }
                        } break;
                    case "StatisticsUpdateInfo":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mStatisticsUpdateInfo.Add(new StatisticsUpdateInfoUnit(xe2.GetAttribute("TableName"), xe2.GetAttribute("DBName"), Convert.ToDateTime(xe2.GetAttribute("StatisticsUpdateDate")), xe2.GetAttribute("StatsName")));
                            }
                        } break;
                    case "MissingIndexes":
                        {
                            foreach (XmlNode xn2 in xe.ChildNodes)
                            {
                                XmlElement xe2 = (XmlElement)xn2;
                                SQLInfoObj.mMissingIndex.Add(new MissingIndexUnit(Convert.ToInt32(xe2.GetAttribute("DBID")), xe2.GetAttribute("CreateIndexStatement"), Convert.ToInt32(xe2.GetAttribute("ImprovementMeasure"))));
                            }
                        } break;
                }
            }
        }
        public static int VersionCompare(string s1, string s2)//Compare the version
        {
            string[] words1 = s1.Split('.');
            string[] words2 = s2.Split('.');
            for (int i = 0; i < words1.Length; i++)
            {
                if (Convert.ToInt32(words1[i]) > Convert.ToInt32(words2[i])) return 1;
                else if (Convert.ToInt32(words1[i]) < Convert.ToInt32(words2[i])) return -1;
            }
            return (words1.Length - words2.Length);
        }

        public static void GenReport(string CompanyName)
        {
            try
            {
                object oMissing = System.Reflection.Missing.Value;
                object oEndOfDoc = "\\endofdoc";
                Word._Application oWord;
                Word._Document oDoc;
                oWord = new Word.Application();
                oWord.Visible = true;
                //Console.WriteLine(System.Environment.CurrentDirectory);

                object oTemplate = /*System.Environment.CurrentDirectory*/ System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase+"\\1707_Summary_report_HC-template.dotx";  //path to the template file. 
                oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing);

                oDoc.Bookmarks["CompanyName"].Range.Text = SQLInfoObj.mCompanyName;
                oDoc.Bookmarks["TimeOfDay"].Range.Text = DateTime.Now.ToShortDateString();
                oDoc.Bookmarks["ServerName"].Range.Text = SQLInfoObj.mComputerName; //replace the book mark with the real data. 
                oDoc.Bookmarks["ServerHardwareConfiguration"].Range.Text = "SQL Server 版本是：" + SQLInfoObj.mSQLSP + "   " + SQLInfoObj.mPlatformBit + "位";
                if (VersionCompare(SQLInfoObj.mSQLSP, "8.00.2282") < 0) oDoc.Bookmarks["ServerHardwareConfiguration"].Range.Text = "\n建议您安装SQL Server 2000 Hotfix， 2282。\n";
                else if (VersionCompare(SQLInfoObj.mSQLSP, "10.50.1600.1") > 0) oDoc.Bookmarks["ServerHardwareConfiguration"].Range.InsertAfter("您的SQL版本较新。\n");//oDoc.Bookmarks["ServerHardwareConfiguration"].Range.Text = "\n您的SQL版本较新。\n";
                else if (VersionCompare(SQLInfoObj.mSQLSP, "10.0.1600.22") >= 0 && VersionCompare(SQLInfoObj.mSQLSP, "10.0.4000.0") < 0) oDoc.Bookmarks["ServerHardwareConfiguration"].Range.Text = "\n建议您安装SQL Server 2008 SP2。\n";
                else if (VersionCompare(SQLInfoObj.mSQLSP, "9.00.1399.00") >= 0 && VersionCompare(SQLInfoObj.mSQLSP, "9.00.5000") <= 0) oDoc.Bookmarks["ServerHardwareConfiguration"].Range.Text = "\n建议您安装 SQL Server 2005 SP4。\n";
                if (SQLInfoObj.mDC != 0)
                    oDoc.Bookmarks["DC"].Range.Text = "您的机器现在是域控制器，我们不推荐SQL和域控制器装在同一个机器上。并且，我们不支持DC和SQL群集安装在同一机器上。\n";
                oDoc.Bookmarks["Protocol"].Range.Text = "您的TCP在端口" + SQLInfoObj.mTCP.ToString() + "开着\n";
                //oDoc.Bookmarks["NeverCheckDB"].Range.Text="以下数据库从来没有检查过过:\n";
                //oDoc.Bookmarks["NoCheckDBMonth"].Range.Text = "以下数据库一个月内没有检查过\n";
                foreach (KeyValuePair<string, DateTime> pair in SQLInfoObj.mDLastKnowGoodCheckDB)
                {
                    //oDoc.Bookmarks["LastKnownGoodCheckDB"].Range.Text = pair.Key + " " + pair.Value.ToString()+"\n";
                    if (pair.Value == Convert.ToDateTime("1900-01-01 00:00:00.000"))
                        //oDoc.Bookmarks["NeverCheckDB"].Range.Text = pair.Key + "\n";
                        oDoc.Bookmarks["NeverCheckDB"].Range.InsertAfter(pair.Key + "\n");
                    //oDoc.Range(oDoc.Bookmarks["NeverCheckDB"], oDoc.Bookmarks["NoCheckDBMonth"]).Text = pair.Key + "\n";
                    else if (DateTime.Now.Subtract(pair.Value).Days > 30)
                        oDoc.Bookmarks["NoCheckDBMonth"].Range.Text = pair.Key + "\n";
                    // oDoc.Range(oDoc.Bookmarks["NoCheckDBMonth"], oDoc.Bookmarks["PrivateKeyBackup"]).Text = pair.Key + "\n";
                }
                //oDoc.Bookmarks["PrivateKeyBackup"].Range.Text = "加密数据库的私有密钥没有备份，该问题可能导致加密数据库无法使用。以下数据库的密钥没有备份：\n";
                if (SQLInfoObj.mPrivateKeyBackup.Count == 0) oDoc.Bookmarks["PrivateKeyBackup"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mPrivateKeyBackup.Count; i++)
                {
                    if (SQLInfoObj.mPrivateKeyBackup[i].dKeyBackupTime == Convert.ToDateTime("1900-01-01 00:00:00.000"))
                        //oDoc.Bookmarks["PrivateKeyBackup"].Range.Text = SQLInfoObj.mPrivateKeyBackup[i].sEncryptedDatabase + " " + SQLInfoObj.mPrivateKeyBackup[i].sCertificateName +"\n";
                        oDoc.Range(oDoc.Bookmarks["PrivateKeyBackup"], oDoc.Bookmarks["DBWithoutBackup"]).Text = SQLInfoObj.mPrivateKeyBackup[i].sEncryptedDatabase + " " + SQLInfoObj.mPrivateKeyBackup[i].sCertificateName + "\n";
                }

                //oDoc.Bookmarks["DBWithoutBackup"].Range.Text = "下列数据库从未备份，或上次备份时间超过30天，请注意备份。\n";
                for (int i = 0; i < SQLInfoObj.mDBNameWithoutBackup.Count; i++)
                {
                    oDoc.Bookmarks["DBWithoutBackup"].Range.Text = SQLInfoObj.mDBNameWithoutBackup[i].ToString() + "\n";
                    //oDoc.Range(oDoc.Bookmarks["DBWithoutBackup"], oDoc.Bookmarks["DBWithoutLogBackup"]).Text = SQLInfoObj.mDBNameWithoutBackup[i].ToString() + "\n";
                }
                //oDoc.Bookmarks["DBWithoutLogBackup"].Range.Text = "下列数据库从未进行日志备份。";
                for (int i = 0; i < SQLInfoObj.mDBNameWithoutLogBackup.Count; i++)
                {
                    oDoc.Bookmarks["DBWithoutLogBackup"].Range.Text = SQLInfoObj.mDBNameWithoutLogBackup[i].ToString() + "\n";
                    //oDoc.Range(oDoc.Bookmarks["DBWithoutLogBackup"], oDoc.Bookmarks["NumOfTempDB"]).Text = SQLInfoObj.mDBNameWithoutLogBackup[i].ToString() + "\n";
                }

                //oDoc.Bookmarks["NumOfTempDB"].Range.Text = "NumOfTempDB "+SQLInfoObj.mNumbOfCPU.ToString()+"\n";
                if (SQLInfoObj.mNumbOfCPU == SQLInfoObj.mNumOfTempdb) oDoc.Bookmarks["NumOfTempDB"].Range.Text = "Tempdb设置良好。\n";
                else oDoc.Bookmarks["NumOfTempDB"].Range.Text = "CPU数目为：" + SQLInfoObj.mNumbOfCPU + "\nTempDB数目为：" + SQLInfoObj.mNumOfTempdb + "\nTempdb 数据文件数目和CPU数目不一致，需要优化。\n";

                if (SQLInfoObj.mSQLTrace > 1) oDoc.Bookmarks["SQLTrace"].Range.Text = "您启用了多个trace，该项设置可能会导致系统的性能问题。\n";
                else oDoc.Bookmarks["SQLTrace"].Range.Text = "您的trace设置良好。\n";


                /*oDoc.Bookmarks["DBConfig"].Range.Text = "DBConfig\n" + "AffinityMask: " + SQLInfoObj.mAffinityMask.ToString() + "\nAWE: " + SQLInfoObj.mAWE.ToString() +
                    "\nC2Audit: " + SQLInfoObj.mC2.ToString() + "\nCLR: " + SQLInfoObj.mCLR.ToString() + "\nCrossDBOwnership: " + SQLInfoObj.mCorssDBOwnership.ToString() + "\nDefaultTrace: " + SQLInfoObj.mDefaultTrace.ToString() +
                    "\nLightweightPooling: " + SQLInfoObj.mLightWeightPooling.ToString() + "\nLocks: " + SQLInfoObj.mLocks.ToString() + "\nMaxDOP: " + SQLInfoObj.mMaxDOP.ToString() + "\nMaxServerMemory: " + SQLInfoObj.mMaxServerMemory +
                    "\nMaxWorkerThreads: " + SQLInfoObj.mMaxWorkerThread.ToString() + "\nNetworkPacketSize: " + SQLInfoObj.mNetworkPacketSize.ToString() + "PriorityBoost: " + SQLInfoObj.mPriorityBoost.ToString() + "\nSetWorkingsetSize: " + SQLInfoObj.mSetWorkingsetSize.ToString();*/
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mAffinityMask != 0 ? "Affinity mask 不是默认值，该项设置会影响系统性能 请参阅: http://technet.microsoft.com/zh-cn/library/ms187104(SQL.90).aspx \n" : "Affinity mask设置正常。\n";
                if (SQLInfoObj.mPhysicalMemory > 4015 && SQLInfoObj.mPlatformBit == 32) oDoc.Bookmarks["DBConfig"].Range.Text = "您现在的内存>4G,您的机器是32位的，建议您启用SQL Server AWE选项。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mC2 != 0 ? "请确认 C2 监控是否为必要的，否则我们建议关闭：http://msdn.microsoft.com/zh-cn/library/ms187634.aspx. \n" : "C2监控配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mCorssDBOwnership != 0 ? "Cross db ownership chaining 启用了，这可能会影响到系统的安全性，请参阅： http://msdn.microsoft.com/en-us/library/ms188694.aspx。 \n" : "Cross db ownership chaining已关闭，配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mDefaultTrace != 0 ? "Default trace 被关闭了，我们建议在一般情况下，请启用默认的跟踪，请参阅： http://msdn.microsoft.com/en-us/library/ms175513.aspx \n" : "为Default trace，配置合理.\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mLocks != 0 ? "lock的参数被修改，该项改动可能导致系统无法执行某类查询。 \n" : "Lock 参数配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mLightWeightPooling != 0 ? "Light weight pooling 默认情况下，应该关闭，请参阅： http://msdn.microsoft.com/en-us/library/ms178074.aspx. \n" : "Light weight pooling配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mMaxDOP == 0 ? "Max degree of paramllelism 被设置成默认值，这往往说明该SQL Server没有被优化过。请联系相关人员优化SQL Server。\n" : "";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mMaxServerMemory == 2147483647 ? "最大可用内存数没有被优化过，这在某些系统上可能导致服务器宕机。\n" : "最大可用内存数已被优化过，配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mMaxWorkerThread != 0 ? "最大的工作线程数目被修改过，这将会是请您的系统面临危险，请确认您需要做该项修改。请参阅：http://msdn.microsoft.com/zh-cn/library/ms187024(SQL.90).aspx. \n" : "最大的工作线程数目没有被修改过，配置合理。 \n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mNetworkPacketSize != 4096 ? "网络包大小被修改，该项修改在某些情况下可以提高系统性能的，但是在32位的系统中很容易用完MTL。请参阅：http://msdn.microsoft.com/zh-cn/library/ms187866(SQL.90).aspx \n" : "网络包大小为默认，配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mPriorityBoost != 0 ? "SQL Server运行在高优先级上，该项设置可能导致系统不稳定，请确认该项设置为必须。\n" : "SQL Server 运行在默认优先级上，配置合理。\n";
                oDoc.Bookmarks["DBConfig"].Range.Text = SQLInfoObj.mSetWorkingsetSize != 0 ? "该项设置在SQL Server 2005之后的版本中已经不再有效。\n" : "";
                //oDoc.Bookmarks["PasswordPolicy"].Range.Text = "下列login的密码策略没有被启用，可能会有安全性的问题。\n";
                if (SQLInfoObj.mLoginNamePasswordPolicy.Count == 0) oDoc.Bookmarks["PasswordPolicy"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mLoginNamePasswordPolicy.Count; i++)
                {
                    oDoc.Bookmarks["PasswordPolicy"].Range.Text = SQLInfoObj.mLoginNamePasswordPolicy[i] + "\n";
                }
                //oDoc.Bookmarks["CheckAdminPermission"].Range.Text = "下列用户组拥有SA的权限，请确认是否为必须。\n";
                if (SQLInfoObj.mCheckAdminPermission.Count == 0)
                    oDoc.Bookmarks["CheckAdminPermission"].Range.Text = "无\n";
                foreach (KeyValuePair<string, int> pair in SQLInfoObj.mCheckAdminPermission)
                {
                    if (pair.Value == 1)
                        oDoc.Bookmarks["CheckAdminPermission"].Range.Text = pair.Key + "\n";
                }
                // oDoc.Bookmarks["BlankPassword"].Range.Text = "下列login拥有空密码。\n";
                if (SQLInfoObj.mLoginNameWithBlankPWD.Count == 0)
                    oDoc.Bookmarks["BlankPassword"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mLoginNameWithBlankPWD.Count; i++)
                {
                    oDoc.Bookmarks["BlankPassword"].Range.Text = SQLInfoObj.mLoginNameWithBlankPWD[i] + "\n";
                }
                //oDoc.Bookmarks["PwdSameAsUserName"].Range.Text = "下列login的密码和用户名一样，存在安全隐患。";
                if (SQLInfoObj.mLoginPwdSameAsUserName.Count == 0)
                    oDoc.Bookmarks["PwdSameAsUserName"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mLoginPwdSameAsUserName.Count; i++)
                {
                    oDoc.Bookmarks["PwdSameAsUserName"].Range.Text = SQLInfoObj.mLoginPwdSameAsUserName[i] + "\n";
                }
                oDoc.Bookmarks["Nonyielding"].Range.Text = SQLInfoObj.mNonYielding != 0 ? "SQL Server存在Non-yielding问题，请联系微软技术支持。\n" : "SQL Server 不存在Non-yielding问题.\n";

                oDoc.Bookmarks["AV"].Range.Text = SQLInfoObj.mAV != 0 ? "SQL Server存在访问违例，请联系微软技术支持 \n" : "没有 Access Violation问题。\n";

                //oDoc.Bookmarks["VirtualLogFile"].Range.Text = "以下数据库存在过多的虚拟文件，这可能导致操作该数据库的操作缓慢。\n";

                int iflag = 0;
                foreach (KeyValuePair<string, int> pair in SQLInfoObj.mVLF)
                {
                    if (pair.Value > 500)
                    {
                        oDoc.Bookmarks["VirtualLogFile"].Range.Text = pair.Key + "\n";
                        iflag++;
                    }
                }
                if (iflag == 0) oDoc.Bookmarks["VirtualLogFile"].Range.Text = "无\n";
                oDoc.Bookmarks["MemoryTrimming"].Range.Text = SQLInfoObj.mMemTrimming > 0 ? "您的系统工作有内存被压缩的情况，请联系微软技术支持。\n" : "没有系统工作内存被压缩，配置合理。\n";
                oDoc.Bookmarks["IOBottleneck"].Range.Text = SQLInfoObj.mIOBottleNeck != 0 ? "您的IO系统存在瓶颈，请联系硬件厂商或优化SQL语句。\n" : "您的IO系统没有瓶颈。\n";
                oDoc.Bookmarks["UserTokenCache"].Range.Text = SQLInfoObj.mUserTokenCacheSize > 100 ? "您的系统存在Userstore_tokenPrem泄露问题，请联系微软技术支持。\n" : "您的系统没有Userstore_tokenPrem泄露问题。 \n";

                //oDoc.Bookmarks["CompatibilityLevel"].Range.Text = "下列数据库的兼容级别和系统兼容级别不一致，可能导致性能问题。\n";
                if (SQLInfoObj.mCompatibilityLevel.Count == 0) oDoc.Bookmarks["CompatibilityLevel"].Range.Text = "无\n";
                foreach (KeyValuePair<string, int> pair in SQLInfoObj.mCompatibilityLevel)
                {
                    oDoc.Bookmarks["CompatibilityLevel"].Range.Text = pair.Key + "\n";
                }
                //oDoc.Bookmarks["AutoClose"].Range.Text = "下列数据库的自动关闭功能被打开，一般情况下，对于生产数据库该项设置并不需要，并且它可能影响性能.\n";
                if (SQLInfoObj.mAutoClose.Count == 0) oDoc.Bookmarks["AutoClose"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mAutoClose.Count; i++)
                {
                    oDoc.Bookmarks["AutoClose"].Range.Text = SQLInfoObj.mAutoClose[i] + "\n";
                }

                //oDoc.Bookmarks["PageVerify"].Range.Text = "下列数据库的Page Verify选项设置为关闭，这可能导致SQL Server无法检测到一些数据库损毁。\n";
                if (SQLInfoObj.mPageVerify.Count == 0) oDoc.Bookmarks["PageVerify"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mPageVerify.Count; i++)
                {
                    oDoc.Bookmarks["PageVerify"].Range.Text = SQLInfoObj.mPageVerify[i] + "\n";
                }

                //oDoc.Bookmarks["GrowByPercentage"].Range.Text = "下列数据库被设置为按照比例增长，该项设置对于大型数据库而言，我们建议设置为按照MB增长。\n";
                if (SQLInfoObj.mGroupByPercentage.Count == 0) oDoc.Bookmarks["GrowByPercentage"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mGroupByPercentage.Count; i++)
                {
                    oDoc.Bookmarks["GrowByPercentage"].Range.Text = SQLInfoObj.mGroupByPercentage[i] + "\n";
                }
                oDoc.Bookmarks["PageLifeExpectancy"].Range.Text = SQLInfoObj.mPageLifeExpectancy < 300 ? "您的SQL Server可能存在内存压力，请做相应调优.\n" : "SQL Server的 PageLifeExpectancy较高，可能不存在内存压力。\n";
                oDoc.Bookmarks["Blocking"].Range.Text = SQLInfoObj.mBlocking != 0 ? "您的系统存在阻塞， 可能影响系统性能. \n" : "您的系统不存在阻塞。\n";
                oDoc.Bookmarks["CPU_UT"].Range.Text = SQLInfoObj.mCPU_UT > 0.8 ? "您的SQL Server现在的CPU使用率很高，请注意。\n" : "你的SQL Server CPU使用率在合理范围内。\n";
                //oDoc.Bookmarks["DiskPerforamnce"].Range.Text = "当前磁盘性能：\n";
                int iavg__transfer_persec = 0;
                foreach (KeyValuePair<DateTime, int> pair in SQLInfoObj.mDiskPerformance)
                {
                    iavg__transfer_persec += pair.Value;
                }
                if (iavg__transfer_persec == 0) oDoc.Bookmarks["DiskPerforamnce"].Range.Text = "未知。\n";
                else if (iavg__transfer_persec < 33) oDoc.Bookmarks["DiskPerforamnce"].Range.Text = "当前系统存在磁盘IO瓶颈。\n";
                else if (iavg__transfer_persec < 100) oDoc.Bookmarks["DiskPerforamnce"].Range.Text = "当前系统磁盘系能OK。\n";
                else oDoc.Bookmarks["DiskPerforamnce"].Range.Text = "当前系统磁盘性能很快。\n";
                oDoc.Bookmarks["DeadlockCheck"].Range.Text = SQLInfoObj.mDeadLock != 0 ? "至少一个数据库存在死锁。\n" : "当前不存在死锁。\n";
                //oDoc.Bookmarks["IndexFrag"].Range.Text = "碎片情况：\n";
                for (int i = 0; i < SQLInfoObj.mIndexFrag.Count; i++)
                {
                    oDoc.Bookmarks["IndexFrag"].Range.Text = "数据库" + SQLInfoObj.mIndexFrag[i].sDBName + "中表" + SQLInfoObj.mIndexFrag[i].sTableName + "上的索引" + SQLInfoObj.mIndexFrag[i].sIndexName + "存在碎片\n";
                }
                //oDoc.Bookmarks["StatisticsUpdateInfo"].Range.Text = "以下统计信息30天内没有更新，请及时更新：\n";
                if (SQLInfoObj.mStatisticsUpdateInfo.Count == 0) oDoc.Bookmarks["StatisticsUpdateInfo"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mStatisticsUpdateInfo.Count; i++)
                {
                    oDoc.Bookmarks["StatisticsUpdateInfo"].Range.Text = "数据库" + SQLInfoObj.mStatisticsUpdateInfo[i].sDBName + "中表" + SQLInfoObj.mStatisticsUpdateInfo[i].sTableName + "上的" + SQLInfoObj.mStatisticsUpdateInfo[i].sStatsName + "\n";
                }
                //oDoc.Bookmarks["MissingIndex"].Range.Text = "建议建立以下统计信息，可使查询效率加快。\n";
                if (SQLInfoObj.mMissingIndex.Count == 0) oDoc.Bookmarks["MissingIndex"].Range.Text = "无\n";
                for (int i = 0; i < SQLInfoObj.mMissingIndex.Count; i++)
                {
                    oDoc.Bookmarks["MissingIndex"].Range.Text = "数据库" + SQLInfoObj.mMissingIndex[i].iDBID.ToString() + "使用语句" + SQLInfoObj.mMissingIndex[i].sCreateIndexStatement + "建立索引，可使查询效率提高%" + SQLInfoObj.mMissingIndex[i].iImprovementMeasure.ToString() + " " + "\n";
                }
                oDoc.Close();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Generate report failed!");
                System.Environment.Exit(0);
            }
        }
    }

    class SQLInfo
    {
        public string mXMLFilePath;
        public string mCompanyName;
        public String mComputerName;
        public String mInstanceName;
        public int mPlatformBit;
        public int mDC;
        public int mTCP;
        public int mNP;
        public String mSQLSP;
        public int mNumOfTempdb;
        public int mNumbOfCPU;
        public int mSQLTrace;
        public int mAffinityMask;
        public int mAWE;
        public int mC2;
        public int mCLR;
        public int mCorssDBOwnership;
        public int mDefaultTrace;
        public int mLightWeightPooling;
        public int mLocks;
        public int mMaxDOP;
        public int mMaxServerMemory;
        public int mMaxWorkerThread;
        public int mNetworkPacketSize;
        public int mPriorityBoost;
        public int mSetWorkingsetSize;
        public int mNonYielding;
        public int mAV;
        public int mMemTrimming;
        public int mIOBottleNeck;
        public int mUserTokenCacheSize;
        public int mPageLifeExpectancy;
        public int mBlocking;
        public int mCPU_UT;
        public int mDeadLock;
        public int mPhysicalMemory;

        //Dictionary or List
        public Dictionary<string, DateTime> mDLastKnowGoodCheckDB = new Dictionary<string, DateTime>();
        public List<string> mDBNameWithoutBackup = new List<string>();
        public List<string> mDBNameWithoutLogBackup = new List<string>();
        public List<string> mLoginNamePasswordPolicy = new List<string>();
        public Dictionary<string, int> mCheckAdminPermission = new Dictionary<string, int>();
        public List<string> mLoginNameWithBlankPWD = new List<string>();
        public List<string> mLoginPwdSameAsUserName = new List<string>();
        public Dictionary<string, int> mVLF = new Dictionary<string, int>();
        public Dictionary<string, int> mCompatibilityLevel = new Dictionary<string, int>();
        public List<string> mAutoClose = new List<string>();
        public List<string> mPageVerify = new List<string>();
        public List<string> mGroupByPercentage = new List<string>();
        public Dictionary<DateTime, int> mDiskPerformance = new Dictionary<DateTime, int>();
        public List<IndexFragUnit> mIndexFrag = new List<IndexFragUnit>();
        public List<StatisticsUpdateInfoUnit> mStatisticsUpdateInfo = new List<StatisticsUpdateInfoUnit>();
        public List<MissingIndexUnit> mMissingIndex = new List<MissingIndexUnit>();
        public List<PrivateKeyBackupUnit> mPrivateKeyBackup = new List<PrivateKeyBackupUnit>();
    }

}

